﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model.AntiFake;
using System.Data.SqlClient;
using DAL.DBUtility;
using System.Data;
using Common;


namespace DAL.AntiFake
{
    public class QueryCodeLogDAL
    {
        #region 查询（获取单个MODEL）
        /// <summary>
        /// 根据主键查询表数据
        /// </summary>
        /// <param name=" ID">string 主键--查询条件</param>
        /// <returns>DataTable 查询结果集</returns>
        public QueryCodeLogModel GetModel(int ID)
        {
            QueryCodeLogModel model = new QueryCodeLogModel();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            strSql.Append("  ID,");
            strSql.Append("Fw_Code ,");
            strSql.Append("QueryType ,");
            strSql.Append("QueryValue ,");
            strSql.Append("QueryTime ,");
            strSql.Append("QueryMessage ,");
            strSql.Append("Valid ,");
            strSql.Append("QueryProvince ,");
            strSql.Append("QueryCity ,");
            strSql.Append("ProductId ,");
            strSql = new StringBuilder(strSql.ToString().Trim(','));
            strSql.Append(" from AntiFake_QueryCodeLog ");
            strSql.Append(" where  ID='" + ID + "'");

            SqlParameter[] parameters = {
					new SqlParameter("@ID",ID)
			};

            SqlDataReader sdr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 添加方法（通过传递实体类添加数据，把添加的数据赋值给实体类属性，不添加的值不填写）
        /// </summary>
        /// <param name="QueryCodeLogModel">QueryCodeLogModel实体类</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public Common.ResultEntity Insert(QueryCodeLogModel model)
        {

            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            int pcount = 0;
            int i = 0;
            if (model.ID != null)
            {
                strSql1.Append("ID,");
                strSql2.Append("@ID ,");
                pcount += 1;
            }
            if (model.Fw_Code != null)
            {
                strSql1.Append("Fw_Code,");
                strSql2.Append("@Fw_Code ,");
                pcount += 1;
            }
            if (model.QueryType != null)
            {
                strSql1.Append("QueryType,");
                strSql2.Append("@QueryType ,");
                pcount += 1;
            }
            if (model.QueryValue != null)
            {
                strSql1.Append("QueryValue,");
                strSql2.Append("@QueryValue ,");
                pcount += 1;
            }
            if (model.QueryTime != null)
            {
                strSql1.Append("QueryTime,");
                strSql2.Append("@QueryTime ,");
                pcount += 1;
            }
            if (model.QueryMessage != null)
            {
                strSql1.Append("QueryMessage,");
                strSql2.Append("@QueryMessage ,");
                pcount += 1;
            }
            if (model.Valid != null)
            {
                strSql1.Append("Valid,");
                strSql2.Append("@Valid ,");
                pcount += 1;
            }
            if (model.QueryProvince != null)
            {
                strSql1.Append("QueryProvince,");
                strSql2.Append("@QueryProvince ,");
                pcount += 1;
            }
            if (model.QueryCity != null)
            {
                strSql1.Append("QueryCity,");
                strSql2.Append("@QueryCity ,");
                pcount += 1;
            }
            if (model.ProductId != null)
            {
                strSql1.Append("ProductId,");
                strSql2.Append("@ProductId ,");
                pcount += 1;
            }
            SqlParameter[] parameters = 
                {
					new SqlParameter("@Fw_Code", model.Fw_Code),
					new SqlParameter("@QueryType", model.QueryType),
					new SqlParameter("@QueryValue", model.QueryValue),
					new SqlParameter("@QueryTime", model.QueryTime),
					new SqlParameter("@QueryMessage", model.QueryMessage),
					new SqlParameter("@Valid", model.Valid),
					new SqlParameter("@QueryProvince", model.QueryProvince),
					new SqlParameter("@QueryCity", model.QueryCity),
					new SqlParameter("@ProductId", model.ProductId),
                 };



            strSql.Append("insert into AntiFake_QueryCodeLog(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            return DbHelperSQL.CommonResult(DbHelperSQL.GetSingle(strSql.ToString().Trim(), parameters));
        }
        #endregion

        #region 修改
        /// <summary>
        /// 根据主键修改数据修改
        /// </summary>
        /// <param name=" ID"> QueryCodeLogModel实体类 需要修改的列赋值给实体类的属性,不修改的不填写</param>
        /// <returns>int 0为失败 ;1为成功</returns>
        public Common.ResultEntity Update(QueryCodeLogModel model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update AntiFake_QueryCodeLog set ");
            if (model.Fw_Code != null)
            {
                strSql.Append("Fw_Code='" + model.Fw_Code + "',");
            }
            if (model.QueryType != null)
            {
                strSql.Append("QueryType='" + model.QueryType + "',");
            }
            if (model.QueryValue != null)
            {
                strSql.Append("QueryValue='" + model.QueryValue + "',");
            }
            if (model.QueryTime != null)
            {
                strSql.Append("QueryTime=CONVERT(datetime,'" + model.QueryTime + "'),");
            }
            if (model.QueryMessage != null)
            {
                strSql.Append("QueryMessage='" + model.QueryMessage + "',");
            }
            if (model.Valid != null)
            {
                strSql.Append("Valid='" + model.Valid + "',");
            }
            if (model.QueryProvince != null)
            {
                strSql.Append("QueryProvince='" + model.QueryProvince + "',");
            }
            if (model.QueryCity != null)
            {
                strSql.Append("QueryCity='" + model.QueryCity + "',");
            }
            if (model.ProductId != null)
            {
                strSql.Append("ProductId='" + model.ProductId + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where ID='" + model.ID + "' ");
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString()));
        }
        #endregion

        #region 删除
        /// <summary>
        /// 通过主键删除方法
        /// </summary>
        /// <param name="  ID">string 主键--删除条件</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public Common.ResultEntity Delete(int? ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from AntiFake_QueryCodeLog ");
            strSql.Append(" where  ID= '" + ID + "'");
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString()));
        }
        #endregion

        #region 公共Model
        /// <summary>
        /// 公共Model
        /// </summary>
        /// <param name="result"></param>
        /// <returns></returns>
        private QueryCodeLogModel CommonModel(SqlDataReader sdr)
        {
            QueryCodeLogModel model = new QueryCodeLogModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.Fw_Code = PageValidate.ConvertToTrimString(sdr["Fw_Code"]);
            model.QueryType = PageValidate.ConvertToTrimInt(sdr["QueryType"]);
            model.QueryValue = PageValidate.ConvertToTrimString(sdr["QueryValue"]);
            model.QueryTime = PageValidate.ConvertToDateTime(sdr["QueryTime"]);
            model.QueryMessage = PageValidate.ConvertToTrimString(sdr["QueryMessage"]);
            model.QueryProvince = PageValidate.ConvertToTrimString(sdr["QueryProvince"]);
            model.QueryCity = PageValidate.ConvertToTrimString(sdr["QueryCity"]);
            model.ProductId = PageValidate.ConvertToTrimInt(sdr["ProductId"]);
            return model;
        }
        #endregion


        #region 查询统计 分页
        public DataTable GetPagerList(ref PagerModel pager, string key)
        {
            DataTable result = new DataTable();
            try
            {
                pager.FldName = " *,[address]=QueryProvince+' '+QueryCity ";
                pager.TblName = @" AntiFake_QueryCodeLog ";
                if (pager.StrCondition == "" || string.IsNullOrEmpty(pager.StrCondition))
                {
                    if (string.IsNullOrEmpty(key))
                        pager.StrCondition = " 1=1 ";
                    else
                        pager.StrCondition = " Fw_Code like '%" + key + "%'";
                }
                if (pager.FldSort == " " || string.IsNullOrEmpty(pager.FldSort))
                {
                    pager.FldSort = " ID ";
                }
                SqlParameter[] parameters = {
                    new SqlParameter("@fldName", pager.FldName),
                    new SqlParameter("@tblName", pager.TblName),
                    new SqlParameter("@fldSort", pager.FldSort),
                    new SqlParameter("@strCondition", pager.StrCondition),
                    new SqlParameter("@pageSize", pager.PageSize),
                    new SqlParameter("@page", pager.Page),
                    DbHelperSQL.AddOutParameter("@curpage", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@pageCount", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@counts", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@retval", DbType.Int32, 4),
                };
                result = DbHelperSQL.RunProcedure("SP_Table_GetPageer", parameters, "dbN").Tables[0];
                if (result != null && result.Rows.Count > 0)
                {
                    pager.Curpage = PageValidate.ConvertToTrimInt(parameters[6].Value);
                    pager.PageSize = PageValidate.ConvertToTrimInt(parameters[7].Value);
                    pager.Counts = PageValidate.ConvertToTrimInt(parameters[8].Value);
                    pager.Retval = PageValidate.ConvertToTrimInt(parameters[9].Value);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
      
        }
        #endregion

        #region 查询统计 详细查询
        public DataTable GetPagerListSel(ref PagerModel pager, QueryCodeLogModel_Sel modelSel)
        {
            DataTable result = new DataTable();
            try
            {
                pager.FldName = " a.*,[address]= QueryProvince+' '+ QueryCity ,p.ProductName  ";
                pager.TblName = @"  AntiFake_QueryCodeLog a left join Product_Product p on a.ProductId=p.ID ";
                StringBuilder StrCondition = new StringBuilder();
                StrCondition.Append(" 1=1 ");
                if (string.IsNullOrEmpty(modelSel.EndTime))
                    modelSel.EndTime = DateTime.Now.ToString();
                if (!string.IsNullOrEmpty(modelSel.StartTime) && modelSel.StartTime!="")
                    StrCondition.Append(" and QueryTime>='" + modelSel.StartTime + "' ");
                if (!string.IsNullOrEmpty(modelSel.EndTime) && modelSel.EndTime != "")
                    StrCondition.Append(" and QueryTime<='" + modelSel.EndTime + "' ");
                if (!string.IsNullOrEmpty(modelSel.QueryType) && modelSel.QueryType != "")
                    StrCondition.Append(" and QueryType='" + modelSel.QueryType + "' ");
                if (!string.IsNullOrEmpty(modelSel.Valid) && modelSel.Valid!="")
                    StrCondition.Append(" and Valid='" + modelSel.Valid + "'");
                if (!string.IsNullOrEmpty(modelSel.Fw_Code))
                    StrCondition.Append(" and  Fw_Code like '%" + modelSel.Fw_Code + "%' ");
                if (!string.IsNullOrEmpty(modelSel.Address))
                    StrCondition.Append(" and (QueryProvince='" + modelSel.Address + "' or  QueryCity='" + modelSel.Address + "')");
                if (!string.IsNullOrEmpty(modelSel.ProductName))
                    StrCondition.Append(" and ProductName='" + modelSel.ProductName + "' ");


                if (pager.FldSort == " " || string.IsNullOrEmpty(pager.FldSort))
                {
                    pager.FldSort = " a.ID ";
                }
                SqlParameter[] parameters = {
                    new SqlParameter("@fldName", pager.FldName),
                    new SqlParameter("@tblName", pager.TblName),
                    new SqlParameter("@fldSort", pager.FldSort),
                    new SqlParameter("@strCondition",  StrCondition.ToString()),
                    new SqlParameter("@pageSize", pager.PageSize),
                    new SqlParameter("@page", pager.Page),
                    DbHelperSQL.AddOutParameter("@curpage", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@pageCount", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@counts", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@retval", DbType.Int32, 4),
                };
                DataTable dt = DbHelperSQL.RunProcedure("SP_Table_GetPageer", parameters, "dbN").Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    pager.Curpage = PageValidate.ConvertToTrimInt(parameters[6].Value);
                    pager.PageSize = PageValidate.ConvertToTrimInt(parameters[7].Value);
                    pager.Counts = PageValidate.ConvertToTrimInt(parameters[8].Value);
                    pager.Retval = PageValidate.ConvertToTrimInt(parameters[9].Value);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region 查询统计 数据导出Excel
        public DataTable GetPagerListExcel(string orderSort)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            strSql.Append(" ID '编号',");
            strSql.Append("Fw_Code '防伪码',");
            strSql.Append("case when(QueryType=0) then '网站' when(QueryType=1) then 'wap' when(QueryType=2) then '短信' when(QueryType=3) then '语音' when(QueryType=4) then '微信'  end as '查询方式',");
            strSql.Append("queryValue '查询返回结果',");
            strSql.Append("QueryTime '查询时间',");
            strSql.Append("QueryValue '返回结果',");
            strSql.Append("QueryMessage '返回信息',");
            strSql.Append("case when(Valid=1) then'有效' else '无效' end '是否有效',");
            strSql.Append("'访问地区'=QueryProvince+' '+QueryCity ");
            strSql.Append(string.Format(" from AntiFake_QueryCodeLog order by {0}", orderSort));
            return DbHelperSQL.GetTable(strSql.ToString());
        }
        #endregion

   

        ////////////////////////////////////////////////////异常查询统计////////////////////////////////////////////////////////////////////////////////

        #region 异常查询统计 分页
        public DataTable GetPagerListAnomaly(ref PagerModel pager, string key)
        {
            DataTable result;
            try
            {
                pager.FldName = " Fw_Code,ProductId,QueryTime,QueryProvince,QueryCity,QueryValue,row_number() over(partition by Fw_Code order by Fw_Code) valid ";
                pager.TblName = @" AntiFake_QueryCodeLog ";
               

                if (string.IsNullOrEmpty(key))
                    pager.StrCondition = " 1=1  and  valid=0";
                else
                    pager.StrCondition = " Fw_Code like '%" + key + "%' and  valid=0";


                if (pager.FldSort == " " || string.IsNullOrEmpty(pager.FldSort))
                {
                    pager.FldSort = " ID ";
                }
                SqlParameter[] parameters = {
                    new SqlParameter("@fldName", pager.FldName),
                    new SqlParameter("@tblName", pager.TblName),
                    new SqlParameter("@fldSort", pager.FldSort),
                    new SqlParameter("@strCondition", pager.StrCondition),
                    new SqlParameter("@pageSize", pager.PageSize),
                    new SqlParameter("@page", pager.Page),
                    DbHelperSQL.AddOutParameter("@curpage", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@pageCount", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@counts", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@retval", DbType.Int32, 4),
                };
                result = DbHelperSQL.RunProcedure("SP_Table_GetPageer", parameters, "dbN").Tables[0];
                if (result != null && result.Rows.Count > 0)
                {
                    pager.Curpage = PageValidate.ConvertToTrimInt(parameters[6].Value);
                    pager.PageSize = PageValidate.ConvertToTrimInt(parameters[7].Value);
                    pager.Counts = PageValidate.ConvertToTrimInt(parameters[8].Value);
                    pager.Retval = PageValidate.ConvertToTrimInt(parameters[9].Value);
                }
              
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region 查询统计 详细查询
        public DataTable GetPagerListAnomalySel(ref PagerModel pager, QueryCodeLogAnomaly_Sel modelSel)
        {
            DataTable result ;
            try
            {
                pager.FldName = " a.*,[address]= QueryProvince+' '+ QueryCity ,p.ProductName ";
                pager.TblName = @"  AntiFake_QueryCodeLog a left join Product_Product p on a.ProductId=p.ID ";
                StringBuilder StrCondition = new StringBuilder();
                StrCondition.Append("  Valid=0 ");
                if (string.IsNullOrEmpty(modelSel.EndTime))
                    modelSel.EndTime = DateTime.Now.ToString();
                if (!string.IsNullOrEmpty(modelSel.StartTime) && modelSel.StartTime != "")
                    StrCondition.Append(" and QueryTime>='" + modelSel.StartTime + "' ");
                if (!string.IsNullOrEmpty(modelSel.EndTime) && modelSel.EndTime != "")
                    StrCondition.Append(" and QueryTime<='" + modelSel.EndTime + "' ");
                if (!string.IsNullOrEmpty(modelSel.Fw_Code))
                    StrCondition.Append(" and  Fw_Code like '%" + modelSel.Fw_Code + "%' ");
                if (!string.IsNullOrEmpty(modelSel.ProductName))
                    StrCondition.Append(" and p.ProductName='" + modelSel.ProductName + "' ");
                if (!string.IsNullOrEmpty(modelSel.ProductId))
                    StrCondition.Append(" and ProductId='" + modelSel.ProductId + "' ");
                if (pager.FldSort == " " || string.IsNullOrEmpty(pager.FldSort))
                {
                    pager.FldSort = " a.ID ";
                }
                SqlParameter[] parameters = {
                    new SqlParameter("@fldName", pager.FldName),
                    new SqlParameter("@tblName", pager.TblName),
                    new SqlParameter("@fldSort", pager.FldSort),
                    new SqlParameter("@strCondition",  StrCondition.ToString()),
                    new SqlParameter("@pageSize", pager.PageSize),
                    new SqlParameter("@page", pager.Page),
                    DbHelperSQL.AddOutParameter("@curpage", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@pageCount", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@counts", DbType.Int32, 4),
                    DbHelperSQL.AddOutParameter("@retval", DbType.Int32, 4),
                };
                result = DbHelperSQL.RunProcedure("SP_Table_GetPageer", parameters, "dbN").Tables[0];
                if (result != null && result.Rows.Count > 0)
                {
                    pager.Curpage = PageValidate.ConvertToTrimInt(parameters[6].Value);
                    pager.PageSize = PageValidate.ConvertToTrimInt(parameters[7].Value);
                    pager.Counts = PageValidate.ConvertToTrimInt(parameters[8].Value);
                    pager.Retval = PageValidate.ConvertToTrimInt(parameters[9].Value);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 异常查询统计 数据导出Excel
        public DataTable GetPagerListAnomalyExcel(string orderSort)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
          
            strSql.Append("Fw_Code '防伪码',");
            strSql.Append(" ProductId '产品代码',");
            strSql.Append(" Convert(datetime,QueryTime,120) '查询日期',");
            strSql.Append(" Convert(varchar(10),QueryTime,108) '查询时间',");
            strSql.Append(" QueryProvince '查询省份',");
            strSql.Append(" QueryCity '查询地区',");
            strSql.Append(" QueryValue '查询者',");
            strSql.Append(" valid '是否异常'");
            strSql.Append(string.Format(" from AntiFake_QueryCodeLog order by {0} where where Valid=0", orderSort));
            return DbHelperSQL.GetTable(strSql.ToString());
        }
        #endregion

        #region 防伪码查询  真假
        public DataTable GetCode( string tbl,string code)
        {
            DataTable result;
            try
            {
                string sql = string.Format("select a.*,c.ProdoctBrand,c.LabelImgUrl,e.EnterpriseName  from AntiFake_Digitcode{0} a  , CodeManage_Card c,Organization_Enterprise e where a.EnterpriseId=c.EnterpriseId and e.ID=a.EnterpriseId and  Flag=1 and Fw_Code={1}" ,tbl, code);
                result = DbHelperSQL.GetTable(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region 查询 查询记录  
        public DataTable GetCodeLogDis(string code)
        {
            DataTable result;
            try
            {
                string strSql = string.Format(@"select  Fw_Code,ProductId,QueryTime,QueryValue,
row_number() over(CreateDate desc) valid  from AntiFake_QueryCodeLog where fw_code='{0}'", code);

                result = DbHelperSQL.GetTable(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
    }
}
